﻿/***************************************************COPYRIGHT***************************************
* Copyright (c) 2008, Whiteboard-IT
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without modification, are permitted provided that 
* the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following 
 * disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following
 * disclaimer in the documentation and/or other materials provided with the distribution.
*
* Neither the name of Whiteboard-IT, LLC nor the names of its contributors may be used to endorse or promote 
 * products derived from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, 
 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON 
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
*****************************************************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using Boo.Lang;

namespace Mite.MsSql
{
	/// <summary>
	/// Adapter for Microsoft Sql Server.
	/// </summary>
	public class MsSqlAdapter : Adapter
	{
		public MsSqlAdapter()
		{
			AlterationValidator = new AlterationValidation();
			QueryFactory = new MsSqlQueryFactory();
		}

		public override Database GetDatabase(IDbConnection connection, string name)
		{
			var builder = new DatabaseBuilder
			              {
			              	Name = name
			              };

			connection.ChangeDatabase(name);

			LoadTables(builder, connection);


			return builder.GetDatabase();
		}

		public override IDbCommand SetSchemaVersion(IDbConnection conn, int i)
		{
			return Insert(conn, MIGRATION_SCHEMA_TABLE_NAME, i, DateTime.Now);
		}

		public override IDbConnection CreateConnection(string host, string database, string username, string password,
		                                               Hash options)
		{
			return new SqlConnection(BuildConnectionString(host, database, username, password, options));
		}

		public override IEnumerable<Database> GetDatabases(IDbConnection conn)
		{
			IList<Database> databases = new List<Database>();


			using (IDbTransaction trans = conn.BeginTransaction())
			{
				IEnumerable<string> databaseNames = GetDatabaseNames(trans);
				databaseNames = RemoveSystemDatabaseNamesFrom(databaseNames);

				foreach (string databaseName in databaseNames)
				{
					conn.ChangeDatabase(databaseName);

					var databaseBuilder = new DatabaseBuilder();
					databaseBuilder.Name = databaseName;

					LoadTables(databaseBuilder, conn);

					databases.Add(databaseBuilder.GetDatabase());
				}

				trans.Commit();
			}

			return databases;
		}

		private static string BuildConnectionString(string host, string database, string username, string password,
		                                            Hash options)
		{
			Debug.Assert(!String.IsNullOrEmpty(host));
			Debug.Assert(!String.IsNullOrEmpty(database));

			string connectionString = String.Format("Data Source={0};Initial Catalog={1};", host,
			                                        database);
			if (String.IsNullOrEmpty(username))
			{
				// Assume integrated security
				connectionString += "Integrated Security=true;";
			}
			else
				connectionString += String.Format("Username={0};Password={1}", username, password);

			return connectionString;
		}

		/// <summary>
		/// Return the names of all databases on the server.  Perform this query as a part of the
		/// transaction <paramref name="trans"/>.
		/// </summary>
		private static IEnumerable<string> GetDatabaseNames(IDbTransaction trans)
		{ 
			IList<string> databaseNames = new List<string>();

			// Query databases
			IDbCommand cmd = new SqlCommand("sp_databases");
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Transaction = trans;
			cmd.Connection = trans.Connection;

			using (IDataReader dr = cmd.ExecuteReader())
			{
				while (dr.Read())
					databaseNames.Add(dr["DATABASE_NAME"].ToString());
			}

			return databaseNames;
		}

		/// <summary>
		/// Returns the names of the tables in the database named <paramref name="databaseName"/>.
		/// </summary>
		private static IEnumerable<string> GetTableNamesInDatabase(string databaseName, IDbConnection conn)
		{
			return MsSqlUtility.GetTableNamesInDatabase(databaseName, conn);
		}

		private IEnumerable<Column> GetColumnsFor(string tableName, IDbConnection conn)
		{
			IDbCommand command = new SqlCommand();
			command.CommandText =
				@"SELECT tables.table_name, columns.column_name, 
				columns.data_type, columns.is_nullable
				FROM information_schema.tables as tables
				JOIN information_schema.columns as columns
				on columns.table_name = tables.table_name
				WHERE tables.table_name = @table_name";
			command.Connection = conn;

			command.Parameters.Add(new SqlParameter("table_name", tableName));

			IEnumerable<Column> columns = null;

			using (IDataReader dr = command.ExecuteReader())
				columns = ReadColumns(dr);

			columns.ForEach(
				column =>
				{
					if (IsPrimaryKey(tableName, column.Name, conn))
						column.PrimaryKey();
					if (IsIdentity(tableName, column.Name, conn))
						column.Identity();

					column.IsUnique = IsUnique(tableName, column.Name, conn);
				});

			return columns;
		}

		/// <summary>
		/// Load tables from the database into the <see cref="DatabaseBuilder"/>.
		/// </summary>
		protected void LoadTables(DatabaseBuilder builder, IDbConnection conn)
		{
			string databaseName = builder.Name;

			IEnumerable<string> tableNames = GetTableNamesInDatabase(databaseName, conn);

			foreach (string tableName in tableNames)
			{
				var tableBuilder = new TableBuilder();
				tableBuilder.Name = tableName;
				tableBuilder.Columns = GetColumnsFor(tableName, conn);

				builder.Tables.Add(tableBuilder.GetTable());
			}
		}

		protected override DbType ParseDbType(string dataType)
		{
			var param = new SqlParameter();
			try
			{
				if (dataType == "numeric") dataType = "decimal"; //MsSqlServer treats these as equivalent
				param.SqlDbType = (SqlDbType) Enum.Parse(typeof (SqlDbType), dataType, true);
				return param.DbType;
			}
			catch (ArgumentException ex)
			{
				throw new Exception("Could not map type: " + dataType, ex);
			}
		}

		private bool IsPrimaryKey(string tableName, string columnName, IDbConnection conn)
		{
			if (_tableNameToPrimaryKeyColumnNames.ContainsKey(tableName))
				return _tableNameToPrimaryKeyColumnNames[tableName].Contains(columnName);

			using (IDbCommand command = GetQueryConstraintsCommand(tableName, "PRIMARY KEY"))
			{
				command.Connection = conn;

				using (IDataReader dr = command.ExecuteReader())
				{
					IList<string> primaryKeyColumns = new List<string>();
					while (dr.Read())
						primaryKeyColumns.Add(dr["column_name"].ToString());

					_tableNameToPrimaryKeyColumnNames.Add(tableName, primaryKeyColumns);

					return primaryKeyColumns.Contains(columnName);
				}
			}
		}

		private bool IsUnique(string tableName, string columnName, IDbConnection conn)
		{
			if (_tableNameToIndexColumnNames.ContainsKey(tableName))
				return _tableNameToIndexColumnNames[tableName].Contains(columnName);

			using (IDbCommand command = GetQueryConstraintsCommand(tableName, "UNIQUE"))
			{
				command.Connection = conn;

				using (IDataReader dr = command.ExecuteReader())
				{
					IList<string> indexColumns = new List<string>();
					while (dr.Read())
						indexColumns.Add(dr["column_name"].ToString());

					_tableNameToIndexColumnNames.Add(tableName, indexColumns);

					return indexColumns.Contains(columnName);
				}
			}
		}

		private bool IsIdentity(string tableName, string columnName, IDbConnection conn)
		{
			using (IDbCommand command = new SqlCommand())
			{
				command.CommandText = "SELECT COLUMNPROPERTY(OBJECT_ID(@table_name), @column_name, 'IsIdentity')";
				command.Connection = conn;

				command.Parameters.Add(new SqlParameter("table_name", tableName));
				command.Parameters.Add(new SqlParameter("column_name", columnName));

				using (IDataReader dr = command.ExecuteReader())
				{
					dr.Read();
					return dr[0].ToBool();
				}
			}
		}

		private IEnumerable<Column> ReadColumns(IDataReader dr)
		{
			IList<Column> columns = new List<Column>();

			while (dr.Read())
			{
				string name = dr["COLUMN_NAME"].ToString();
				string dataType = dr["DATA_TYPE"].ToString();
				bool isNullable = dr["IS_NULLABLE"].ToBool();

				var column = new Column(name, ParseDbType(dataType))
				             {
				             	IsNullable = isNullable
				             };

				columns.Add(column);
			}

			return columns;
		}

		/// <summary>
		/// It is the responsibility of the caller to dispose of the command returned by this method.
		/// </summary>
		private SqlCommand GetQueryConstraintsCommand(string tableName, string constraintType)
		{
			var command = new SqlCommand();
			command.CommandText =
				@"select key_usages.column_name 
					from information_schema.table_constraints as table_constraints 
					join information_schema.key_column_usage as key_usages
					on table_constraints.constraint_name = key_usages.constraint_name
					where table_constraints.constraint_type = @constraint_type
					and key_usages.table_name = @table_name;";

			command.Parameters.Add(new SqlParameter("table_name", tableName));
			command.Parameters.Add(new SqlParameter("constraint_type", constraintType));

			return command;
		}

		protected override IDbCommand Insert(IDbConnection conn, string tableName, params object[] values)
		{
			IDbCommand cmd = conn.CreateCommand();

			cmd.CommandText = String.Format("INSERT INTO {0} VALUES({1})",
			                                tableName,
			                                String.Join(", ",
			                                            values.Select(
			                                            	v =>
			                                            	v is string || v is DateTime ? "'" + v + "'" : v.ToString())
			                                            	.ToArray()));
			return cmd;
		}

		private static IEnumerable<string> RemoveSystemDatabaseNamesFrom(IEnumerable<string> databaseNames)
		{
			string[] systemDatabaseNames = {"msdb", "tempdb", "master", "Commons"};
			return
				from name in databaseNames
				where !systemDatabaseNames.Contains(name)
				select name;
		}

		private readonly IDictionary<string, IEnumerable<string>> _tableNameToIndexColumnNames =
			new Dictionary<string, IEnumerable<string>>();

		private readonly IDictionary<string, IEnumerable<string>> _tableNameToPrimaryKeyColumnNames =
			new Dictionary<string, IEnumerable<string>>();
	}
}